# _*_ coding: utf-8 _*_
# @Date: 2020/8/19 18:20
# @Author: 刘利 18603013656
# @File: get_db.py
# @Software: PyCharm

import sqlite3


def get_name():
    """从数据库中获取公司名"""
    con = sqlite3.connect(r'../spider_51job_company/finance_company.db')
    cur = con.cursor()

    data = cur.execute('select company from security').fetchall()
    names = []
    for i in range(len(data)):
        names.append(data[i][0])
    return names


def home():
    con1 = sqlite3.connect(r'../spider_51job_WeakManager/email_spider/company.db')
    cur1 = con1.cursor()

    con2 = sqlite3.connect(r'../spider_51job_WeakManager/spider_51job_company/company.db')
    cur2 = con2.cursor()

    data1 = cur2.execute('''select id, company, company_51url from Interior_Design''').fetchall()
    # print('start:', len(cur2.execute('''select id from home''').fetchall()))

    # cur1.execute('''create table home (id integer primary key autoincrement, company, company_url)''')
    num = 0
    for item in data1:
        # print(item)
        cur1.execute('''insert into home (company, company_url) values (?, ?)''', (item[0], item[1]))
        # cur2.execute('''delete from home where company=:value1''', {'value1': item[0]})
        # cur2.execute('''update home set company=:value1 where id=:value2''', {'value1': item[0], 'value2': num})
        # cur2.execute('''insert into home (id, company, company_url) values (?, ?, ?)''', (item[0], item[1], item[2]))
        num += 1
        # cur1.execute('''update Comp set id=:value1 where company=:value2''', {'value1': num, 'value2': item[0]})

    # print('end:', len(cur2.execute('''select id from home''').fetchall()))
    print('num: ', num)
    con1.commit()
    con2.commit()

    cur1.close()
    con1.close()

    cur2.close()
    con2.close()


def delete_db():
    """删除重复的公司名数据,并将id重新排序"""
    con = sqlite3.connect('email_spider/company.db')
    cur = con.cursor()

    cur.execute('''
    delete from main.security where security.rowid not in 
    (select MAX(security.rowid) from main.security group by company);''')

    data = cur.execute('''select company from main.security''').fetchall()
    for i in range(len(data)):
        cur.execute('''update security set id=:value2 where company=:value1''', {'value1': data[i][0], 'value2': i + 1})

    con.commit()
    cur.close()
    con.close()


if __name__ == '__main__':
    delete_db()
